package dao;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bean.Content;
import bean.Fundraising_project;
import database.Database;

public class Fundraising_projectDao {
	public ArrayList<Fundraising_project> getAllFundraising_project() {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select id,project_title,fundraising_project_date,scan_num from fundraising_project order by fundraising_project_date desc";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			ArrayList<Fundraising_project> fundraising_project_list = new ArrayList<Fundraising_project>();
			while (rs.next()) {
				Fundraising_project fundraising_project = new Fundraising_project();
				fundraising_project.setId(rs.getInt(1));
				fundraising_project.setProject_title(rs.getString(2));
				fundraising_project.setFundraising_project_date(rs.getString(3)
						.substring(0, 10));
				fundraising_project.setScan_num(rs.getInt(4));
				fundraising_project_list.add(fundraising_project);
			}
			return fundraising_project_list;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public ArrayList queryConditionFundraising_project(String message,
			String date_start, String date_end)
			throws UnsupportedEncodingException {
		Connection conn = null;
		ArrayList<Fundraising_project> fundraising_project_list = new ArrayList<Fundraising_project>();
		try {
			conn = Database.getConnection();
			String temp_sql = "";
			if (date_start != null && !"".equals(date_start)
					&& date_end != null && !"".equals(date_end)) {
				temp_sql = "and fundraising_project_date between '"
						+ date_start + "' and '" + date_end + "'";
			}
			String sql = "select id,project_title,fundraising_project_date,scan_num from fundraising_project where project_title like '%"
					+ message
					+ "%' "
					+ temp_sql
					+ " order by fundraising_project_date desc";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			while (rs.next()) {
				Fundraising_project fundraising_project = new Fundraising_project();
				fundraising_project.setId(rs.getInt(1));
				fundraising_project.setProject_title(rs.getString(2));
				fundraising_project.setFundraising_project_date(rs.getString(3)
						.substring(0, 10));
				fundraising_project.setScan_num(rs.getInt(4));
				fundraising_project_list.add(fundraising_project);
			}
			prep.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return fundraising_project_list;
	}

	/**
	 * 
	 * @param title
	 * @param date
	 * @return
	 */
	public int deleteFundraising_projectItem(String id) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "delete from fundraising_project where id = " + id;
			PreparedStatement prep = conn.prepareStatement(sql);
			int num = prep.executeUpdate();
			prep.close();
			return num;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}

	public Content getContent(String id, int index) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select id,project_title,data,fundraising_project_date,scan_num from fundraising_project where id="
					+ id;
			PreparedStatement prep = null;
			if (index == 2) {
				String sql2 = "update fundraising_project set scan_num = scan_num + 1 where id = "
						+ id;
				prep = conn.prepareStatement(sql2);
				prep.executeUpdate();
			}
			prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();
			rs.next();
			Content content = new Content();
			content.setId(rs.getInt(1));
			content.setTitle(rs.getString(2));

			String data = rs.getString(3);
			if (data != null) {
				data = data.replaceAll("\r\n", "");
				data = data.replaceAll("\r", "");
				data = data.replaceAll("\n", "");
				data = data.replaceAll("\"", "'");
			}
			content.setData(data);
			content.setDate(rs.getString(4));
			content.setScan_num(rs.getInt(5));

			prep.close();
			return content;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	public int updateContent(String id, String modifyTitle, String data) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "update fundraising_project set project_title=? , data=? where id="
					+ id;
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, modifyTitle);
			prep.setString(2, data);
			int num = prep.executeUpdate();

			prep.close();
			return num;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}

	public ArrayList<String> getAllTitles() {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select distinct project_title from fundraising_project";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			ArrayList<String> project_title_list = new ArrayList<String>();
			while (rs.next()) {
				String project_title = rs.getString(1);
				project_title_list.add(project_title);
			}

			return project_title_list;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	public int insertItem(String insertTitle, String data, String date) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "insert into fundraising_project(project_title,fundraising_project_date,data,scan_num) values(?,?,?,?);";
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, insertTitle);
			prep.setString(2, date);
			prep.setString(3, data);
			prep.setInt(4, 0);
			int num = prep.executeUpdate();

			prep.close();
			return num;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}
}
